package qldc.pm.tienan.service.persistence;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import qldc.pm.tienan.constants.TrangThaiTienAnTrongThongKeEnum;
import qldc.pm.tienan.model.DoiTuongTienAn;
import qldc.pm.tienan.model.TienAn;
import qldc.pm.tienan.model.impl.TienAnImpl;

import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.QueryUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.portal.kernel.dao.orm.Type;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.util.CalendarUtil;
import com.liferay.portal.kernel.util.OrderByComparator;
import com.liferay.portal.kernel.util.StringPool;
import com.liferay.portal.kernel.util.StringUtil;
import com.liferay.portal.kernel.util.Validator;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;

public class DoiTuongTienAnFinderImpl extends
		BasePersistenceImpl<DoiTuongTienAn> implements DoiTuongTienAnFinder {
	private static Log _log = LogFactoryUtil.getLog(DoiTuongTienAnFinder.class);

	public static final String COUNT_BY_SDDCN_CMND_HVT_SSHK_DCTT = DoiTuongTienAnFinder.class
			.getName() + ".countBySDDCN_CMND_HVT_SSHK_DCTT";
	public static final String FIND_BY_SDDCN_CMND_HVT_SSHK_DCTT = DoiTuongTienAnFinder.class
			.getName() + ".findBySDDCN_CMND_HVT_SSHK_DCTT";

	public static final String COUNT_BY_SSHK_DCTT = DoiTuongTienAnFinder.class
			.getName() + ".countBySSHK_DCTT_NGAYTAO";

	public static final String COUNT_BY_DS_XOA_AN_TICH = DoiTuongTienAnFinder.class
			.getName() + ".countByDS_XoaAnTich";
	public static final String FIND_BY_DS_XOA_AN_TICH = DoiTuongTienAnFinder.class
			.getName() + ".findByDS_XoaAnTich";

	public static final String COUNT_BY_LICH_SU_PHAM_TOI = DoiTuongTienAnFinder.class
			.getName() + ".countByLichSuPhamToi";
	public static final String FIND_BY_LICH_SU_PHAM_TOI = DoiTuongTienAnFinder.class
			.getName() + ".findByLichSuPhamToi";

	public static final String FIND_BY_DS_HO_GIA_DINH = DoiTuongTienAnFinder.class
			.getName() + ".findByDS_HoGiaDinh";

	public static final String COUNT_BY_SO_HO_KHAU = DoiTuongTienAnFinder.class
			.getName() + ".countBySoHoKhau";
	public static final String FIND_BY_SO_HO_KHAU = DoiTuongTienAnFinder.class
			.getName() + ".findBySoHoKhau";

	public static final String COUNT_BY_DS_TAI_PHAM = DoiTuongTienAnFinder.class
			.getName() + ".countByDS_TaiPham";
	public static final String FIND_BY_DS_TAI_PHAM = DoiTuongTienAnFinder.class
			.getName() + ".findByDS_TaiPham";
	// Vung dia ly

	public static final String FINDDS_HO_GIA_DINH_BY_VUNG_DIA_LY_THOI_GIAN = DoiTuongTienAnFinder.class
			.getName() + ".findDsHoGiaDinhByVungDiaLy_ThoiGianToaAn";
	public static final String FIND_DS_TIEN_AN_BY_VUNG_DIA_LY_THOI_GIAN = DoiTuongTienAnFinder.class
			.getName() + ".findDsTienAnByVungDiaLy_ThoiGian";
	public static final String COUNT_DS_HO_GIA_DINH_BY_VUNG_DIA_LY_THOI_GIAN = DoiTuongTienAnFinder.class
			.getName() + ".countDsHoGiaDinhByVungDiaLy_ThoiGianToaAn";
	public static final String COUNT_DS_TIEN_AN_BY_VUNG_DIA_LY_THOI_GIAN = DoiTuongTienAnFinder.class
			.getName() + ".countDsTienAnByVungDiaLy_ThoiGian";
	public static final String FINDTIENANBYSOSOHOKHAU = DoiTuongTienAnFinder.class
			.getName() + ".findTienAnBySoSoHoKhau";
	public static final String FIND_NGAY_TOA_AN_BY_SO_HO_KHAU = DoiTuongTienAnFinder.class
			.getName() + ".findNgayToaAn_bySoHoKhau";

	@SuppressWarnings("unchecked")
	public int countBySDDCN_CMND_HVT_SSHK_DCTT(String soDinhDanhCaNhan,
			String soCMND, String hoVaTen, String soSoHoKhau, long tinhId,
			long quanHuyenId, long phuongXaId, String diaChi,
			Date tuNgayPhamToi, Date denNgayPhamToi, String banAnSo,
			Date tuNgayToaAn, Date denNgayToaAn, int trangThai)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_SDDCN_CMND_HVT_SSHK_DCTT);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql, "AND (ntt.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql, "AND (ntt.quanHuyenId = ?)", StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql, "AND (ntt.phuongXaId = ?)", StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayPhamToi >= ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayToaAn >= ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayToaAn <= ?)", StringPool.BLANK);
			}

			if (trangThai == TrangThaiTienAnTrongThongKeEnum.TAT_CA.getValue()) {
				sql = StringUtil.replace(sql, "AND (dtts.xoaAnTich = ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + soDinhDanhCaNhan + StringPool.PERCENT);
			qpos.add(soDinhDanhCaNhan);

			qpos.add(StringPool.PERCENT + soCMND + StringPool.PERCENT);
			qpos.add(soCMND);

			qpos.add(StringPool.PERCENT + hoVaTen + StringPool.PERCENT);
			qpos.add(hoVaTen);

			qpos.add(StringPool.PERCENT + soSoHoKhau + StringPool.PERCENT);
			qpos.add(soSoHoKhau);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			if (trangThai == TrangThaiTienAnTrongThongKeEnum.DA_XOA_AN_TICH.getValue()) {
				qpos.add(true);
			} else if (trangThai == TrangThaiTienAnTrongThongKeEnum.DANG_THI_HANH_AN.getValue()) {
				qpos.add(false);
			}

			qpos.add(StringPool.PERCENT + banAnSo + StringPool.PERCENT);
			qpos.add(banAnSo);

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(denNgayPhamToi);
				calendar.add(Calendar.DATE, 1);
				denNgayPhamToi = CalendarUtil.getGTDate(calendar);

				qpos.add(denNgayPhamToi);
			}

			if (Validator.isNotNull(tuNgayToaAn)) {
				qpos.add(tuNgayToaAn);
			}

			if (Validator.isNotNull(denNgayToaAn)) {
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(denNgayToaAn);
				calendar.add(Calendar.DATE, 1);
				denNgayToaAn = CalendarUtil.getGTDate(calendar);

				qpos.add(denNgayToaAn);
			}

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}

			return 0;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findBySDDCN_CMND_HVT_SSHK_DCTT(
			String soDinhDanhCaNhan, String soCMND, String hoVaTen,
			String soSoHoKhau, long tinhId, long quanHuyenId, long phuongXaId,
			String diaChi, Date tuNgayPhamToi, Date denNgayPhamToi,
			String banAnSo, Date tuNgayToaAn, Date denNgayToaAn, int trangThai,
			int start, int end, OrderByComparator obc) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_SDDCN_CMND_HVT_SSHK_DCTT);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql, "AND (ntt.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql, "AND (ntt.quanHuyenId = ?)", StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql, "AND (ntt.phuongXaId = ?)", StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayPhamToi >= ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayToaAn >= ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (ts.ngayToaAn <= ?)", StringPool.BLANK);
			}

			if (trangThai == TrangThaiTienAnTrongThongKeEnum.TAT_CA.getValue()) {
				sql = StringUtil.replace(sql, "AND (dtts.xoaAnTich = ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			sql = CustomSQLUtil.replaceOrderBy(sql, obc);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("doiTuongTienAnId", Type.LONG);
			q.addScalar("soDinhDanhCaNhan", Type.STRING);
			q.addScalar("xoaAnTich", Type.BOOLEAN);
			q.addScalar("hoVaTen", Type.STRING);
			q.addScalar("soCMND", Type.STRING);
			q.addScalar("tinhId", Type.LONG);
			q.addScalar("quanHuyenId", Type.LONG);
			q.addScalar("phuongXaId", Type.LONG);
			q.addScalar("diaChi", Type.STRING);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + soDinhDanhCaNhan + StringPool.PERCENT);
			qpos.add(soDinhDanhCaNhan);

			qpos.add(StringPool.PERCENT + soCMND + StringPool.PERCENT);
			qpos.add(soCMND);

			qpos.add(StringPool.PERCENT + hoVaTen + StringPool.PERCENT);
			qpos.add(hoVaTen);

			qpos.add(StringPool.PERCENT + soSoHoKhau + StringPool.PERCENT);
			qpos.add(soSoHoKhau);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			if (trangThai == TrangThaiTienAnTrongThongKeEnum.DA_XOA_AN_TICH.getValue()) {
				qpos.add(true);
			} else if (trangThai == TrangThaiTienAnTrongThongKeEnum.DANG_THI_HANH_AN.getValue()) {
				qpos.add(false);
			}

			qpos.add(StringPool.PERCENT + banAnSo + StringPool.PERCENT);
			qpos.add(banAnSo);

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(denNgayPhamToi);
				calendar.add(Calendar.DATE, 1);
				denNgayPhamToi = CalendarUtil.getGTDate(calendar);

				qpos.add(denNgayPhamToi);
			}

			if (Validator.isNotNull(tuNgayToaAn)) {
				qpos.add(tuNgayToaAn);
			}

			if (Validator.isNotNull(denNgayToaAn)) {
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(denNgayToaAn);
				calendar.add(Calendar.DATE, 1);
				denNgayToaAn = CalendarUtil.getGTDate(calendar);

				qpos.add(denNgayToaAn);
			}

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public int countBySSHK_DCTT(String soSoHoKhau, Date tuNgayPhamToi,
			Date denNgayPhamToi, long tinhId, long quanHuyenId,
			long phuongXaId, String diaChi) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_SSHK_DCTT);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi > ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + soSoHoKhau + StringPool.PERCENT);
			qpos.add(soSoHoKhau);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				qpos.add(denNgayPhamToi);
			}

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}

			return 0;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public int countByDS_XoaAnTich(long tinhId, long quanHuyenId,
			long phuongXaId, Date tuNgayXoaAnTich, Date denNgayXoaAnTich)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_DS_XOA_AN_TICH);

			if (tinhId <= 0) {
				return 0;
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayXoaAnTich)) {
				sql = StringUtil.replace(sql,
						"AND (TS_LichSuXoaAnTich.ngayKyDuyetXoaAnTich >= ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgayXoaAnTich)) {
				sql = StringUtil.replace(sql,
						"AND (TS_LichSuXoaAnTich.ngayKyDuyetXoaAnTich <= ?)",
						StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayXoaAnTich)) {
				qpos.add(tuNgayXoaAnTich);
			}

			if (Validator.isNotNull(denNgayXoaAnTich)) {
				qpos.add(denNgayXoaAnTich);
			}

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}

			return 0;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findByDS_XoaAnTich(long tinhId, long quanHuyenId,
			long phuongXaId, Date tuNgayXoaAnTich, Date denNgayXoaAnTich,
			int start, int end, OrderByComparator order) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_DS_XOA_AN_TICH);

			if (tinhId <= 0) {
				return new ArrayList<Object[]>();
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayXoaAnTich)) {
				sql = StringUtil.replace(sql,
						"AND (TS_LichSuXoaAnTich.ngayKyDuyetXoaAnTich >= ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgayXoaAnTich)) {
				sql = StringUtil.replace(sql,
						"AND (TS_LichSuXoaAnTich.ngayKyDuyetXoaAnTich <= ?)",
						StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);
			if (null != order) {
				sql = CustomSQLUtil.replaceOrderBy(sql, order);
			}

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("soSoHoKhau", Type.STRING);
			q.addScalar("tinhId", Type.LONG);
			q.addScalar("quanHuyenId", Type.LONG);
			q.addScalar("phuongXaId", Type.LONG);
			q.addScalar("diaChi", Type.STRING);
			q.addScalar("hoVaTen", Type.STRING);
			q.addScalar("soDinhDanhCaNhan", Type.STRING);
			q.addScalar("soCMND", Type.STRING);
			q.addScalar("toiDanh", Type.STRING);
			q.addScalar("ngayPhamToi", Type.STRING);
			q.addScalar("soLanPhamToi", Type.LONG);
			q.addScalar("ngaySinh", Type.STRING);
			q.addScalar("gioiTinh", Type.INTEGER);


			QueryPos qpos = QueryPos.getInstance(q);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayXoaAnTich)) {
				qpos.add(tuNgayXoaAnTich);
			}

			if (Validator.isNotNull(denNgayXoaAnTich)) {
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(denNgayXoaAnTich);
				calendar.add(Calendar.DATE, 1);
				denNgayXoaAnTich = CalendarUtil.getGTDate(calendar);
				qpos.add(denNgayXoaAnTich);
			}

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public int countByLichSuPhamToi(String soDinhDanhCaNhan, String hoVaTen,
			long tinhId, long quanHuyenId, long phuongXaId, String diaChi)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_LICH_SU_PHAM_TOI);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + soDinhDanhCaNhan + StringPool.PERCENT);
			qpos.add(soDinhDanhCaNhan);
			qpos.add(StringPool.PERCENT + hoVaTen + StringPool.PERCENT);
			qpos.add(hoVaTen);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}

			return 0;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findByLichSuPhamToi(String soDinhDanhCaNhan,
			String hoVaTen, long tinhId, long quanHuyenId, long phuongXaId,
			String diaChi, int start, int end) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_LICH_SU_PHAM_TOI);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("doiTuongTienAnId", Type.LONG);
			q.addScalar("tinhId", Type.LONG);
			q.addScalar("quanHuyenId", Type.LONG);
			q.addScalar("phuongXaId", Type.LONG);
			q.addScalar("diaChi", Type.STRING);
			q.addScalar("hoVaTen", Type.STRING);
			q.addScalar("soDinhDanhCaNhan", Type.STRING);
			q.addScalar("soLanPhamToi", Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + soDinhDanhCaNhan + StringPool.PERCENT);
			qpos.add(soDinhDanhCaNhan);
			qpos.add(StringPool.PERCENT + hoVaTen + StringPool.PERCENT);
			qpos.add(hoVaTen);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public int countBySoHoKhau(long tinhId, long quanHuyenId, long phuongXaId,
			String diaChi, Date tuNgayPhamToi, Date denNgayPhamToi)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_SO_HO_KHAU);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi > ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				qpos.add(denNgayPhamToi);
			}

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}

			return 0;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findBySoHoKhau(long tinhId, long quanHuyenId,
			long phuongXaId, String diaChi, Date tuNgayPhamToi,
			Date denNgayPhamToi, int start, int end) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_SO_HO_KHAU);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi > ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);
			q.addScalar("soSoHoKhau", Type.STRING);
			q.addScalar("tinhId", Type.LONG);
			q.addScalar("quanHuyenId", Type.LONG);
			q.addScalar("phuongXaId", Type.LONG);
			q.addScalar("diaChi", Type.STRING);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				qpos.add(denNgayPhamToi);
			}

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
	@SuppressWarnings("unchecked")
	public List<TienAn> findTienAnBySoSoHoKhau(String soSoHoKhau) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FINDTIENANBYSOSOHOKHAU);

			if (Validator.isNull(soSoHoKhau)) {
				return new ArrayList<TienAn>();
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);
			SQLQuery q = session.createSQLQuery(sql);
			q.addEntity("TienAn", TienAnImpl.class);
			QueryPos qpos = QueryPos.getInstance(q);
			qpos.add(soSoHoKhau);

			return (List<TienAn>) QueryUtil.list(q, getDialect(), -1, -1);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
	@SuppressWarnings("unchecked")
	public List<Object[]> findByDS_HoGiaDinh(String soSoHoKhau)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_DS_HO_GIA_DINH);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("hoVaTen", Type.STRING);
			q.addScalar("ngaySinh", Type.DATE);
			q.addScalar("gioiTinh", Type.INTEGER);
			q.addScalar("soCMND", Type.STRING);
			q.addScalar("dmdcQuanHeGiaDinhId", Type.LONG);
			q.addScalar("ngayPhamToi", Type.STRING);
			q.addScalar("toiDanh", Type.STRING);
			q.addScalar("hinhPhatChinh", Type.STRING);
			q.addScalar("chuThich", Type.STRING);
			q.addScalar("soLanPhamToi", Type.LONG);
			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + soSoHoKhau + StringPool.PERCENT);

			return (List<Object[]>) QueryUtil.list(q, getDialect(), -1, -1);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public int countByDS_TaiPham(Date tuNgayPhamToi, Date denNgayPhamToi,
			long tinhId, long quanHuyenId, long phuongXaId, String diaChi)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_DS_TAI_PHAM);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi > ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);
			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				qpos.add(denNgayPhamToi);
			}

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}

			return 0;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findByDS_TaiPham(Date tuNgayPhamToi,
			Date denNgayPhamToi, long tinhId, long quanHuyenId,
			long phuongXaId, String diaChi, int start, int end)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_DS_TAI_PHAM);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi > ?)", StringPool.BLANK);
			}

			if (Validator.isNull(denNgayPhamToi)) {
				sql = StringUtil.replace(sql,
						"AND (TS_TienAn.ngayPhamToi <= ?)", StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("soSoHoKhau", Type.STRING);
			q.addScalar("tinhId", Type.LONG);
			q.addScalar("quanHuyenId", Type.LONG);
			q.addScalar("phuongXaId", Type.LONG);
			q.addScalar("diaChi", Type.STRING);
			q.addScalar("hoVaTen", Type.STRING);
			q.addScalar("soDinhDanhCaNhan", Type.STRING);
			q.addScalar("soCMND", Type.STRING);
			q.addScalar("ngayPhamToi", Type.STRING);
			q.addScalar("toiDanh", Type.STRING);
			q.addScalar("toiDanhTruoc", Type.STRING);
			q.addScalar("ngayPhamToiTruoc", Type.STRING);
			q.addScalar("ngayXoaAnTich", Type.STRING);
			q.addScalar("nhanXet", Type.STRING);

			QueryPos qpos = QueryPos.getInstance(q);

			qpos.add(StringPool.PERCENT + diaChi + StringPool.PERCENT);
			qpos.add(diaChi);
			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayPhamToi)) {
				qpos.add(tuNgayPhamToi);
			}

			if (Validator.isNotNull(denNgayPhamToi)) {
				qpos.add(denNgayPhamToi);
			}

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findDSHoGiaDinhByVungDiaLy_ThoiGianToaAn(long tinhId,
			long quanHuyenId, long phuongXaId, Date tuNgayToaAn,
			Date denNgayToaAn, int start, int end, OrderByComparator order)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil
					.get(FINDDS_HO_GIA_DINH_BY_VUNG_DIA_LY_THOI_GIAN);
			if (tinhId <= 0) {
				return new ArrayList<Object[]>();

			}

			if (quanHuyenId <= 0) {
				sql = StringUtil
						.replace(
								sql,
								"AND (TS_PhieuThongTinVeNguoiCoTienAn.noiThuongTruQuanHuyenId = ?)",
								StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil
						.replace(
								sql,
								"AND (TS_PhieuThongTinVeNguoiCoTienAn.noiThuongTruPhuongXaId = ?)",
								StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND TS_TienAn.ngayToaAn >= ?",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND TS_TienAn.ngayToaAn <= ?",
						StringPool.BLANK);
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, true);

			if (null != order) {
				sql = CustomSQLUtil.replaceOrderBy(sql, order);
			}

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("noiThuongTruTinhId", Type.LONG);
			q.addScalar("noiThuongTruQuanHuyenId", Type.LONG);
			q.addScalar("noiThuongTruPhuongXaId", Type.LONG);
			q.addScalar("noiThuongTruDiaChi", Type.STRING);
			q.addScalar("soSoHoKhau", Type.STRING);
			q.addScalar("hoVaTenChuHo", Type.STRING);
			q.addScalar("soLuongNguoiPhamToi", Type.INTEGER);

			QueryPos qpos = QueryPos.getInstance(q);

			if (Validator.isNotNull(tuNgayToaAn)) {
				qpos.add(tuNgayToaAn);
			}

			if (Validator.isNotNull(denNgayToaAn)) {
				qpos.add(denNgayToaAn);
			}
			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findDsTienAnByVungDiaLy_ThoiGian(long tinhId,
			long quanHuyenId, long phuongXaId, Date tuNgayToaAn,
			Date denNgayToaAn, int xoaAnTich, int start, int end,
			OrderByComparator order) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil
					.get(FIND_DS_TIEN_AN_BY_VUNG_DIA_LY_THOI_GIAN);
			if (tinhId <= 0) {
				return new ArrayList<Object[]>();

			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (TS_TienAn.ngayToaAn >= ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (TS_TienAn.ngayToaAn <= ?)",
						StringPool.BLANK);
			}
			if (xoaAnTich == TrangThaiTienAnTrongThongKeEnum.TAT_CA.getValue()) {
				sql = StringUtil.replace(sql, "AND (TS_TienAn.xoaAnTich = ?)",
						StringPool.BLANK);
			}

			if (null != order) {
				sql = CustomSQLUtil.replaceOrderBy(sql, order);
			}
			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("tinhId", Type.LONG);
			q.addScalar("quanHuyenId", Type.LONG);
			q.addScalar("phuongXaId", Type.LONG);
			q.addScalar("diaChi", Type.STRING);
			q.addScalar("hoVaTen", Type.STRING);
			q.addScalar("soDinhDanhCaNhan", Type.STRING);
			q.addScalar("soCMND", Type.STRING);
			q.addScalar("toiDanh", Type.STRING);
			q.addScalar("ngayPhamToi", Type.STRING);
			q.addScalar("soLanPhamToi", Type.LONG);
			q.addScalar("ngaySinh", Type.STRING);

			QueryPos qpos = QueryPos.getInstance(q);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayToaAn)) {
				qpos.add(tuNgayToaAn);
			}

			if (Validator.isNotNull(denNgayToaAn)) {
				qpos.add(denNgayToaAn);
			}
			if (xoaAnTich == TrangThaiTienAnTrongThongKeEnum.DA_XOA_AN_TICH
					.getValue()) {
				qpos.add(true);
			} else if (xoaAnTich == TrangThaiTienAnTrongThongKeEnum.DANG_THI_HANH_AN
					.getValue()) {
				qpos.add(false);
			}

			return (List<Object[]>) QueryUtil.list(q, getDialect(), start, end);
		} catch (Exception e) {
			_log.info(e);
		} finally {
			closeSession(session);
		}

		return null;
	}

	@SuppressWarnings("unchecked")
	public int countDSHoGiaDinhByVungDiaLy_ThoiGianToaAn(long tinhId,
			long quanHuyenId, long phuongXaId, Date tuNgayToaAn,
			Date denNgayToaAn) {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil
					.get(COUNT_DS_HO_GIA_DINH_BY_VUNG_DIA_LY_THOI_GIAN);

			if (tinhId <= 0) {
				return 0;
			}

			if (Validator.isNull(tuNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND TS_TienAn.ngayToaAn >= ?",
						StringPool.BLANK);
			}
			if (Validator.isNull(denNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND TS_TienAn.ngayToaAn <= ?",
						StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil
						.replace(
								sql,
								"AND (TS_PhieuThongTinVeNguoiCoTienAn.noiThuongTruQuanHuyenId = ?)",
								StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil
						.replace(
								sql,
								"AND (TS_PhieuThongTinVeNguoiCoTienAn.noiThuongTruPhuongXaId = ?)",
								StringPool.BLANK);
			}

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			if (Validator.isNotNull(tuNgayToaAn)) {
				qpos.add(tuNgayToaAn);
			}

			if (Validator.isNotNull(denNgayToaAn)) {
				qpos.add(denNgayToaAn);
			}

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}
		} catch (Exception e) {

		} finally {
			closeSession(session);
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	public int countDsTienAnByVungDiaLy_ThoiGian(long tinhId, long quanHuyenId,
			long phuongXaId, Date tuNgayToaAn, Date denNgayToaAn, int xoaAnTich)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil
					.get(COUNT_DS_TIEN_AN_BY_VUNG_DIA_LY_THOI_GIAN);
			if (tinhId <= 0) {
				return 0;
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);
			}

			if (phuongXaId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.phuongXaId = ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(tuNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (TS_TienAn.ngayToaAn >= ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgayToaAn)) {
				sql = StringUtil.replace(sql, "AND (TS_TienAn.ngayToaAn <= ?)",
						StringPool.BLANK);
			}
			if (xoaAnTich == TrangThaiTienAnTrongThongKeEnum.TAT_CA.getValue()) {
				sql = StringUtil.replace(sql, "AND (TS_TienAn.xoaAnTich = ?)",
						StringPool.BLANK);
			}

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (phuongXaId > 0) {
				qpos.add(phuongXaId);
			}

			if (Validator.isNotNull(tuNgayToaAn)) {
				qpos.add(tuNgayToaAn);
			}

			if (Validator.isNotNull(denNgayToaAn)) {
				qpos.add(denNgayToaAn);
			}
			if (xoaAnTich == TrangThaiTienAnTrongThongKeEnum.DA_XOA_AN_TICH
					.getValue()) {
				qpos.add(true);
			} else if (xoaAnTich == TrangThaiTienAnTrongThongKeEnum.DANG_THI_HANH_AN
					.getValue()) {
				qpos.add(false);
			}
			Iterator<Long> iterator = q.list().iterator();

			if (iterator.hasNext()) {
				Long count = iterator.next();

				if (Validator.isNotNull(count)) {
					return count.intValue();
				}
			}
		} catch (Exception e) {
			_log.info(e);
		} finally {
			closeSession(session);
		}
		return 0;
	}

	
	@SuppressWarnings("unchecked")
	public Date findLatestNgayToaAn_bySoHoKhau(String soHoKhau)
			throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil
					.get(FIND_NGAY_TOA_AN_BY_SO_HO_KHAU);
			
			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar("ngayToaAn", Type.DATE);


			QueryPos qpos = QueryPos.getInstance(q);

			if (Validator.isNotNull(soHoKhau)) {
				qpos.add(soHoKhau);
			}

		

			return (Date) q.list().get(0);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
}
